package com.alvin.abp.service;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.alvin.abp.common.constant.UserRoleType;
import com.alvin.abp.common.util.EncryptUtils;
import com.alvin.abp.domain.AlvinUser;
import com.alvin.abp.domain.Gender;
import com.alvin.abp.domain.PointType;
import com.alvin.abp.domain.Record;
import com.alvin.abp.domain.Rule;

@Service(value="userService")
public class UserService {
	
	private NamedParameterJdbcTemplate jdbcTemplate;

	@Autowired
	private ServiceHelper serviceHelper;

	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}
	
	public AlvinUser readUserById(Long id) {

		String sql = "SELECT `ID`, `USER_NAME`, `BABY_BIRTH`, `GENDER`, `MOBILE`, `WEIXIN`, `PASSWORD`, `ROLE`, `BALANCE` FROM T_MEMBER WHERE `ID`=?";
		try {
			AlvinUser user = (AlvinUser) jdbcTemplate.getJdbcOperations()
					.queryForObject(sql, new Object[] { id },
							new UserRowMapper());
			return user;
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}

	}
	
	public AlvinUser loadUserBasicInfo(String mobile) {
		String sql = "SELECT `ID`, `USER_NAME`, `BABY_BIRTH`, `GENDER`, `MOBILE`, `PASSWORD`, `ROLE`, `BALANCE` FROM T_MEMBER WHERE `MOBILE`=?";
		try {
			AlvinUser alvinUser = (AlvinUser) jdbcTemplate.getJdbcOperations()
					.queryForObject(sql, new Object[] { mobile },
							new UserRowMapper());
			return alvinUser;
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}		
	}
	
	public AlvinUser loadUserByName(String name) {
		String sql = "SELECT `ID`, `USER_NAME`, `BABY_BIRTH`, `GENDER`, `MOBILE`, `WEIXIN`, `PASSWORD`, `ROLE`, `BALANCE` FROM T_MEMBER WHERE `MOBILE`=?";
		try {
			AlvinUser user = (AlvinUser) jdbcTemplate.getJdbcOperations()
					.queryForObject(sql, new Object[] { name },
							new UserRowMapper());
			return user;
		} catch (EmptyResultDataAccessException ex) {
			return null;
		}
	}
	
	public List<AlvinUser> readUsers(){
		String sql = "SELECT `ID`, `USER_NAME`, `BABY_BIRTH`, `GENDER`, `MOBILE`, `PASSWORD`, `ROLE`, `BALANCE` FROM T_MEMBER";
		List<AlvinUser> users = jdbcTemplate.getJdbcOperations().query(sql,
				new UserRowMapper());
		return users;
	}
	
	public void addUser(AlvinUser user){
		user.setRole(UserRoleType.ROLE_MEMBER);
		user.setBalance(BigDecimal.ZERO);
		Date now = new Date();
		user.setDateCreated(now);
		user.setPassword(EncryptUtils.encryptMD5(user.getPassword()));
		String sql = "INSERT INTO T_MEMBER (`USER_NAME`, `GENDER`,`PASSWORD`,`ROLE`,`MOBILE`, `BALANCE`, `BABY_BIRTH`, `INSERT_TIME`, `VISIBLE`) VALUES (:userName, :gender, :password, :role, :mobile, :balance, :babyBirth, :dateCreated, 1)";
		serviceHelper.insert(jdbcTemplate, sql, user);
	}
	
	public void register(String mobile, int balance){
		String sql = "INSERT INTO T_MEMBER (`MOBILE`, `GENDER`, `BALANCE`, `INSERT_TIME`, `VISIBLE`) VALUES (?, ?, ?, ?, 1)";
		jdbcTemplate.getJdbcOperations().update(sql, new Object[] {mobile, Gender.MALE.getName(), balance, new Date()});
	}
	
	public AlvinUser updateUser(AlvinUser user) {
		String sql = "UPDATE T_MEMBER SET `USER_NAME`=:userName, `GENDER`=:gender, `MOBILE`=:mobile, `ROLE`=:role, `PASSWORD`=:password WHERE `ID`=:id";
		user = serviceHelper.update(jdbcTemplate, sql, user);
		return user;
	}
	
	public AlvinUser updateBalance(AlvinUser user) {
		String sql = "UPDATE T_MEMBER SET `BALANCE`=:balance WHERE `ID`=:id";
		user = serviceHelper.update(jdbcTemplate, sql, user);
		return user;
	}
	
	public void deleteUser(Long id) {
		String sql = "DELETE FROM T_MEMBER WHERE ID=?";
		if(!AlvinUser.DEFAULT_SA_ID.equals(id)){
			jdbcTemplate.getJdbcOperations().update(sql, new Object[]{id});
		}
	}
	
	public AlvinUser readSystemUser(){
		String sql = "SELECT `ID`, `USER_NAME`, `BABY_BIRTH`, `GENDER`, `MOBILE`, `PASSWORD`, `ROLE` FROM T_MEMBER LIMIT 1";
		AlvinUser user = (AlvinUser) jdbcTemplate.getJdbcOperations()
				.queryForObject(sql,new UserRowMapper());
		return user;
		
	}
	
	public List<Record> readRecords(Long userId) {
		String sql = "SELECT `ID`, `BALANCE`, `MEMBER_ID`, `POINT`, `TYPE`, `INSERT_TIME` FROM T_RECORD WHERE MEMBER_ID=?";
		List<Record> records = jdbcTemplate.getJdbcOperations().query(sql, new Object[]{userId}, new RecordRowMapper());
		return records;		
	}
	
	static class UserRowMapper implements RowMapper<AlvinUser> {
		public AlvinUser mapRow(ResultSet rs, int rowNum) throws SQLException {
			AlvinUser user = new AlvinUser();
			user.setId(rs.getLong("ID"));
			user.setUserName(rs.getString("USER_NAME"));
			
			Gender gender = null;
			String genderStr = rs.getString("GENDER");
			gender = Gender.valueOf(genderStr);
			user.setGender(gender);
			
			UserRoleType role = null;
			String roleStr = rs.getString("ROLE");
			if(roleStr != null){
				 role = UserRoleType.valueOf(roleStr);
			}
			user.setRole(role);
			
			user.setPassword(rs.getString("PASSWORD"));
			user.setMobile(rs.getString("MOBILE"));
			user.setBalance(rs.getBigDecimal("BALANCE"));
			user.setBabyBirth(rs.getDate("BABY_BIRTH"));
			return user;
		}
	}
	
    static class RecordRowMapper implements RowMapper<Record> {

        public Record mapRow(ResultSet rs, int rowNum) throws SQLException {
        	Record record = new Record();
        	record.setId(rs.getLong("ID"));
        	record.setBalance(rs.getInt("BALANCE"));
        	record.setRecordDate(rs.getDate("INSERT_TIME"));
        	
        	Rule rule = new Rule();
        	try{
        		rule.setType(PointType.values()[rs.getInt("TYPE")]);
        	}catch(NullPointerException ex){
        	}
        	rule.setPoint(rs.getInt("POINT"));
        	record.setRule(rule);
        	
            return record;

        }

    }	
	
}
